from sqlalchemy import create_engine
from urllib import parse

# engine = create_engine("mssql+pymssql://admin:abc123qwe@127.0.0.1,2014/TomNet.ProjTest", deprecate_large_types=True)  # 替换自己的user/pwd/host/db #sa/abc123@/127.0.01,2014/TomNet.ProjTest

engine = create_engine('mssql+pymssql://sa:%s@127.0.0.1:2014/TomNet.ProjTest' % parse.unquote_plus('abc123@'))

# 添加数据
# # m = MetaData()
# # t = Table('t', m, Column('id', Integer, primary_key=True),
# #           Column('x', Integer))
# #
# # m.create_all(engine)
# # engine.execute(t.insert(), {'id': 1, 'x': 1}, {'id': 2, 'x': 2})
import pyecharts.options as opts
from example.commons import Collector
from pyecharts.charts import Line, Page
from py_linq import Enumerable

from connection_db.model.FundDaily import FundDaily
from sqlalchemy.orm import sessionmaker

DBSession = sessionmaker(bind=engine)
session = DBSession()

    # # 筛选
    # user = query.get(1) # 根据主键获取print query.filter(User.user_name == 2)  # 只显示sql语句，不会执行查询print query.filter(User.user_name == 'lujianxing').all()  # 执行查询print query.filter(User.user_name == 'lujianxing', User.accout == 1245678, User.age > 10).all()  # 执行查询print query.filter(User.user_name == 'lujianxing').filter(User.accout == 1245678).all()
    # print query.filter("user_name = 'lujianxing'").all()  # 执行查询print query.filter("user_name = 'lujianxing' and accout=1245678").all()  # 执行查询
    # query2 = session.query(User.user_name)  # 返回的结果不是User的实例，而是元组print query2.all()  # 执行查询print query2.offset(1).limit(1).all()  # 等于 limit 1,1# 排序print query2.order_by(User.user_name).all()
    # print query2.order_by('user_name').all()
    # print query2.order_by(User.user_name.desc()).all()
    # print query2.order_by(User.user_name, User.accout.desc()).all()
    # print query2.filter("user_name = 'lujianxing' and accout=1245678").count()
    # # 聚合查询print session.query(func.count('*')).select_from(User).scalar()
    # print session.query(func.count('1')).select_from(User).scalar()
    # print session.query(func.count(User.id)).scalar()
    # print session.query(func.count('*')).filter(User.id > 0).scalar() # filter() 中包含 User，因此不需要指定表print session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1 # 可以用 limit() 限制 count() 的返回数print session.query(func.sum(User.id)).scalar()
    # print session.query(func.now()).scalar() # func 后可以跟任意函数名，只要该数据库支持print session.query(func.current_timestamp()).scalar()
    # print session.query(func.md5(User.name)).filter(User.id == 1).scalar()


C = Collector()


@C.funcs
def line_base() -> Line:
    query = session.query(FundDaily)

    users = query.filter(FundDaily.code == '519915').order_by(FundDaily.GetTime.asc()).all()
    # userc = Enumerable(users).order_by(FundDaily.GetTime.asc())
    # userc = Enumerable(users).select(lambda x: {x.GetTime, x.NetValue}).to_list()
    name = Enumerable(users).first().FundName  # Enumerable(users).first()

    c = (
        Line()

            .add_xaxis(Enumerable(users).select(lambda x: x.GetTime).to_list())
            .add_yaxis(name, y_axis=Enumerable(users).select(lambda x: x.NetValue)
                       , markpoint_opts=opts.MarkPointOpts(
                data=[opts.MarkPointItem(type_="max"), opts.MarkPointItem(type_="min")])
                       , markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_="average")])
                       # .add_yaxis("商家B", Enumerable(userc).select(lambda x: x.NetValue))
                       )
            .set_global_opts(title_opts=opts.TitleOpts(title="")) #title="Line-基本示例")
        # .options()
    )

    # c = (
    #     Line()
    #     .add(
    #         ["DQ"],
    #         userc,
    #         singleaxis_opts=opts.SingleAxisOpts(type_="time", pos_bottom="10%"),
    #     )
    #     .set_global_opts(title_opts=opts.TitleOpts(title="ThemeRiver-基本示例"))
    # )

    return c

# @C.funcs
# def pie_base() -> Pie:
#     c = (
#         Pie()
#         .add("", [list(z) for z in zip(Faker.choose(), Faker.values())])
#         .set_global_opts(title_opts=opts.TitleOpts(title="Pie-基本示例"))
#         .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
#     )
#     return c

Page().add(*[fn() for fn, _ in C.charts]).render()
print('ok')
# 只显示sql语句，不会执行查询print query[0]
# 执行查询print query.all()
# 执行查询print query.first()
# 执行查询for user in query:  # 执行查询print user.user_name
